﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;

using System.Globalization;

namespace System
{
    public class SqlHelper
    {
        public static string GetListSql(string fields, string filter, string tableName, DBType dbType)
        {
            StringBuilder strSQL = new StringBuilder(100);
            strSQL.Append("SELECT " + fields + " FROM " + FormatDB(tableName, dbType) + "(NOLOCK)");
            if (!string.IsNullOrEmpty(filter))
            {
                strSQL.Append(" WHERE " + filter);
            }
            return strSQL.ToString();
        }


        public static string FormatDB(string name, DBType dbType)
        {
            if (string.IsNullOrEmpty(name))
                return "*";
            if (name.Trim() == "*")
                return "*";

            string strTemp = name;
            strTemp = strTemp.Replace(" ", "").Replace("[", "").Replace("]", "").Replace("'", "");

            switch (dbType)
            {
                case DBType.Sql:
                    strTemp = "[" + strTemp + "]";
                    break;
                case DBType.Sql2005:
                    strTemp = "[" + strTemp + "]";
                    break;
                case DBType.Oracle:
                    strTemp = "" + strTemp + "";
                    break;
                default:
                    strTemp = "[" + strTemp + "]";
                    break;
            }


            return strTemp;
        }

        public static string GetDeleteSql(string filter, string tableName, DBType dbType)
        {
            return ("DELETE FROM " + FormatDB(tableName, dbType) + " WHERE " + filter);
        }


        public static string GetDefaultFilter(BaseModel model, DBType dbType, ref DBParameter parameter)
        {
            string primaryKey = model.GetPrimaryKey();

            parameter = new DBParameter(primaryKey, primaryKey + "Bake", CModel.GetProValue(primaryKey, model));

            List<DBParameter> infos = new List<DBParameter>();
            infos.Add(parameter);
            return GetDefaultFilter(infos, dbType);
        }


        public static string GetDefaultFilter(List<DBParameter> parameters, DBType dbType)
        {
            if (parameters == null || parameters.Count <= 0) return "1=1";

            StringBuilder sbFilter = new StringBuilder(30);
            foreach (DBParameter parameter in parameters)
            {
                sbFilter.Append(FormatDB(parameter.SourceColumn, dbType) + "=" + FormatVariable(dbType) + GetParameterName(parameter) + " AND ");
            }
            sbFilter.Remove(sbFilter.Length - 4, 4);

            return "(" + sbFilter.ToString() + ")";
        }


        public static string GetDefaultFilter(DBType dbType, List<DbParameter> parameters)
        {
            if (parameters == null || parameters.Count <= 0) return "1=1";

            StringBuilder sbFilter = new StringBuilder(30);
            foreach (DbParameter parameter in parameters)
            {
                sbFilter.Append(FormatDB(parameter.SourceColumn, dbType) + "=" + FormatVariable(dbType) + GetParameterName(parameter) + " AND ");
            }
            sbFilter.Remove(sbFilter.Length - 4, 4);

            return "(" + sbFilter.ToString() + ")";
        }

        public static string GetParameterName<T>(T fieldInfo) where T : IDataParameter, IDbDataParameter
        {
            string result = string.Empty;

            if (!string.IsNullOrEmpty(fieldInfo.ParameterName))
            {
                result = fieldInfo.ParameterName;
            }
            else
            {
                result = fieldInfo.SourceColumn;
            }

            return result;
        }


        public static string GetUpdateSql(ref DBParameter parameter, string field, string filter, string tableName, object value, DBType dbType)
        {
            field = field.Trim();
            parameter = new DBParameter(field, "obj" + field, value);
            if (value.GetType() == typeof(byte[]))
            {
                parameter.DbType = DbType.Binary;
            }
            string sql = "UPDATE " + FormatDB(tableName, dbType) + " SET " + FormatFields(parameter.SourceColumn, dbType) + "=" + FormatVariable(dbType) + parameter.ParameterName + " WHERE " + filter;
            return sql;
        }


        public static string GetUpdateSql(BaseModel model, string fields, string tableName, List<DBParameter> parameters, DBType dbType)
        {
            if (model.GetModifyInfos() == null || model.GetModifyInfos().Count <= 0)
                return string.Empty;
            if (parameters == null)
                parameters = new List<DBParameter>();

            DBParameter info = null;
            string filter = SqlHelper.GetDefaultFilter(model, dbType, ref info);

            parameters.Add(info);

            return GetUpdateSql(model, fields, filter, tableName, parameters, dbType);
        }


        public static string GetUpdateSql(BaseModel model, string fields, string filter, string tableName, List<DBParameter> parameters, DBType dbType)
        {
            if (model.GetModifyInfos() == null || model.GetModifyInfos().Count <= 0)
                return string.Empty;

            if (parameters == null)
                parameters = new List<DBParameter>();

            fields = BaseHelper.GetDefaultValue(fields, "*");

            List<string> ListFields = StringHelper.GetListFromString(fields);

            StringBuilder sbSql = new StringBuilder(500);
            sbSql.Append("UPDATE " + FormatDB(tableName, dbType) + " SET ");


            foreach (KeyValuePair<string, ModifyInfo> keyValue in model.GetModifyInfos())
            {
                ModifyInfo modifyInfo = keyValue.Value;

                if (fields == "*" || StringHelper.IsFieldEnable(ListFields, modifyInfo.Field))
                {
                    DBParameter parameter = new DBParameter(modifyInfo.Field, modifyInfo.Field + "Update", modifyInfo.NewValue);

                    DataHelper.SetParameterDbType(parameter, modifyInfo.DbType, modifyInfo);

                    if (modifyInfo.NewValue == null)
                    {
                        parameter.Value = DBNull.Value;
                    }

                    sbSql.Append(FormatFields(parameter.SourceColumn, dbType) + "=" + FormatVariable(dbType) + parameter.ParameterName + ",");

                    parameters.Add(parameter);
                }
            }

            return sbSql.ToString().Substring(0, sbSql.Length - 1) + " WHERE " + filter;
        }


        public static string GetAddSql(BaseModel model, string fields, string tableName, List<DBParameter> parameters, DBType dbType)
        {
            if (model.GetModifyInfos() == null || model.GetModifyInfos().Count <= 0)
                return string.Empty;
            if (parameters == null)
                parameters = new List<DBParameter>();

            fields = BaseHelper.GetDefaultValue(fields, "*");

            List<string> ListFields = StringHelper.GetListFromString(fields);

            StringBuilder txt1 = new StringBuilder(500);
            StringBuilder txt2 = new StringBuilder(500);

            txt1.Append("INSERT INTO " + SqlHelper.FormatDB(tableName, dbType) + " (");
            txt2.Append(") VALUES (");

            foreach (KeyValuePair<string, ModifyInfo> keyValue in model.GetModifyInfos())
            {
                ModifyInfo modifyInfo = keyValue.Value;

                if (fields == "*" || StringHelper.IsFieldEnable(ListFields, modifyInfo.Field))
                {
                    DBParameter parameter = new DBParameter(modifyInfo.Field, modifyInfo.Field + "Add", modifyInfo.NewValue);

                    DataHelper.SetParameterDbType(parameter, modifyInfo.DbType, modifyInfo);

                    if (modifyInfo.NewValue == null)
                    {
                        parameter.Value = DBNull.Value;
                    }

                    txt1.Append(FormatFields(parameter.SourceColumn, dbType));
                    txt1.Append(",");
                    txt2.Append(FormatVariable(dbType) + parameter.ParameterName + ",");

                    parameters.Add(parameter);
                }
            }

            return (txt1.ToString().Substring(0, txt1.Length - 1) + txt2.ToString().Substring(0, txt2.Length - 1) + ") ");
        }


        public static string GetInsertOrUpdateSql(BaseModel model, string fields, string tableName, List<DBParameter> parameters, DBType dbType)
        {
            if (model.GetModifyInfos() == null || model.GetModifyInfos().Count <= 0)
                return string.Empty;
            if (parameters == null)
                parameters = new List<DBParameter>();

            string commandText = "";

            switch (dbType)
            {
                case DBType.Sql:
                    commandText = GetSQLInsertOrUpdateSql(model, fields, tableName, parameters, dbType);
                    break;
                case DBType.Sql2005:
                    commandText = GetSQLInsertOrUpdateSql(model, fields, tableName, parameters, dbType);
                    break;
                case DBType.Oracle:
                    commandText = GetOracleInsertOrUpdateSql(model, fields, tableName, parameters, dbType);
                    break;
                default:
                    commandText = GetSQLInsertOrUpdateSql(model, fields, tableName, parameters, dbType);
                    break;
            }


            return commandText.ToString();
        }


        private static string GetSQLInsertOrUpdateSql(BaseModel model, string fields, string tableName, List<DBParameter> parameters, DBType dbType)
        {
            StringBuilder commandText = new StringBuilder();

            string primaryKey = model.GetPrimaryKey();

            string primaryKeyValue = CModel.GetProValue(model, primaryKey);

            DBParameter parameter = new DBParameter(primaryKey, "@InsertOrUpdatePrimaryKey", primaryKeyValue);
            parameters.Add(parameter);

            commandText.Append("IF(@InsertOrUpdatePrimaryKey IN (SELECT " + SqlHelper.FormatDB(primaryKey, dbType) + " FROM " + SqlHelper.FormatDB(tableName, dbType) + "))");
            commandText.Append(" BEGIN ");
            commandText.Append(SqlHelper.GetUpdateSql(model, fields, tableName, parameters, dbType));
            commandText.Append(" END ");
            commandText.Append(" ELSE ");
            commandText.Append(" BEGIN ");
            commandText.Append(SqlHelper.GetAddSql(model, fields, tableName, parameters, dbType));
            commandText.Append(" END ");

            return commandText.ToString();
        }

        private static string GetOracleInsertOrUpdateSql(BaseModel model, string fields, string tableName, List<DBParameter> parameters, DBType dbType)
        {
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" BEGIN ");
            commandText.Append(SqlHelper.GetUpdateSql(model, fields, tableName, parameters, dbType));
            commandText.Append("; IF SQL%NOTFOUND THEN ");
            commandText.Append(SqlHelper.GetAddSql(model, fields, tableName, parameters, dbType));
            commandText.Append("; END IF; ");
            commandText.Append(" END;");

            return commandText.ToString();
        }

        public static List<DBParameter> ConvertDbParameters(List<DbParameter> parameters)
        {
            List<DBParameter> result = new List<DBParameter>();

            if (parameters != null)
            {
                foreach (DbParameter parameter in parameters)
                {
                    result.Add(ConvertDbParameter(parameter));
                }

            }

            return result;
        }


        public static DBParameter ConvertDbParameter(DbParameter parameter)
        {
            DBParameter result = null;

            if (parameter != null)
            {
                result = new DBParameter(parameter.SourceColumn, parameter.ParameterName, parameter.Value);

                result.DbType = parameter.DbType;
                result.Direction = parameter.Direction;
                result.Size = parameter.Size;
                result.SourceVersion = parameter.SourceVersion;

            }

            return result;
        }


        public static string FormatFields(string fieldList, DBType dbType)
        {
            if (string.IsNullOrEmpty(fieldList))
                return "*";
            if (fieldList.Trim() == "*")
                return "*";

            string strTemp = fieldList;

            strTemp = strTemp.Replace(" ", "").Replace("[", "").Replace("]", "");

            switch (dbType)
            {
                case DBType.Sql:
                    strTemp = "[" + strTemp + "]";
                    strTemp = strTemp.Replace('，', ',').Replace(",", "],[").Replace("[]", "").Replace(",,,", ",").Replace(",,", ",");
                    break;
            }
            return strTemp;
        }

        /// <summary>
        /// 获取数据操作语句中的变量名
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static string FormatVariable(DBType dbType)
        {
            switch (dbType)
            {
                case DBType.Sql:
                    return "@";
                case DBType.Sql2005:
                    return "@";
                case DBType.Oracle:
                    return ":";
                default:
                    return "@";
            }
        }


        public static string FormatSql(string fields, string filter, string sorts, string tableName, DBType dbType)
        {
            return "SELECT " + FormatFields(fields, dbType) + " FROM " + FormatDB(tableName, dbType) + "(NOLOCK) WHERE " + filter + " ORDER BY " + sorts;
        }

        public static string FormatSql(string fields, string filter, string sorts, string tableName, DBType dbType, int count)
        {
            return "SELECT TOP " + count + " " + FormatFields(fields, dbType) + " FROM " + FormatDB(tableName, dbType) + "(NOLOCK) WHERE " + filter + " ORDER BY " + sorts;
        }

        public static string FormatSql(string fields, string filter, string tableName, DBType dbType)
        {
            return "SELECT " + FormatFields(fields, dbType) + " FROM " + FormatDB(tableName, dbType) + "(NOLOCK) WHERE " + filter;
        }

        public static string FormatSql(string fields, string filter, string tableName, DBType dbType, int count)
        {
            return "SELECT TOP " + count + " " + FormatFields(fields, dbType) + " FROM " + FormatDB(tableName, dbType) + " WHERE " + filter;
        }

        public static string GetPageViewSql(int? pageIndex, int? pageSize, string fields, string filter, string sorts, string tableName, string primaryKey, DBType dbType)
        {
            string pageField = primaryKey;

            SqlHelper.FormatArg(ref pageIndex, ref pageSize, ref fields, ref filter, ref sorts, pageField);
            string sql = "";
            switch (dbType)
            {
                case DBType.Sql:
                    sql = GetSql2000PageViewSql(pageIndex, pageSize, fields, filter, sorts, tableName, primaryKey, dbType);
                    break;
                case DBType.Sql2005:
                    sql = GetSql2005PageViewSql(pageIndex, pageSize, fields, filter, sorts, tableName, primaryKey, dbType);
                    break;
                case DBType.Oracle:
                    sql = GetOraclePageViewSql(pageIndex, pageSize, fields, filter, sorts, tableName, primaryKey, dbType);
                    break;
            }
            return sql;
        }

        private static string GetSql2000PageViewSql(int? pageIndex, int? pageSize, string fields, string filter, string sorts, string tableName, string primaryKey, DBType dbType)
        {
            string sql = "";

            if (!string.IsNullOrEmpty(sorts))
            {
                sorts = " ORDER BY " + sorts;
            }

            if (!string.IsNullOrEmpty(filter))
            {
                filter = string.Format(CultureInfo.InvariantCulture, " WHERE {0}", filter);
            }

            if (pageIndex == 1)
            {
                sql =
                    string.Format(CultureInfo.InvariantCulture, "SELECT TOP {0} {1} FROM {2}{3}{4} SELECT @RecordCount=COUNT({5}) FROM {2}{3}", pageSize, fields, FormatDB(tableName, dbType), filter, sorts, primaryKey);
            }
            else
            {

                sql = "SET ROWCOUNT @Top declare @s varchar(4000) SELECT @Top=@Top-1,@s = CASE WHEN @Top<@pageSize THEN @s+', ' + QUOTENAME(RTRIM(CAST({0}  as varchar(8000))),'''') ELSE '' END FROM {1} {2}{3}";
                sql += " SET @s = STUFF(@s, 1, 1, '') if(len(@s)>0) EXEC ('Select Top {4} {5} From {1} Where {0} in('+@s+') {3}') SELECT @RecordCount=COUNT(0) FROM {1}{2}";
                sql = string.Format(CultureInfo.InvariantCulture, sql, "[" + primaryKey + "]", FormatDB(tableName, dbType), filter, sorts, pageSize, fields);
            }
            return sql;
        }

        private static string GetSql2005PageViewSql(int? pageIndex, int? pageSize, string fields, string filter, string sorts, string tableName, string primaryKey, DBType dbType)
        {
            string sql = "";

            if (!string.IsNullOrEmpty(sorts))
            {
                sorts = " ORDER BY " + sorts;
            }

            if (!string.IsNullOrEmpty(filter))
            {
                filter = string.Format(CultureInfo.InvariantCulture, " WHERE {0}", filter);
            }

            sql = string.Format(CultureInfo.InvariantCulture, "SELECT {0} FROM (SELECT ROW_NUMBER() OVER({1}) AS ROWNUM, * FROM {2}(NOLOCK) {3}) AS T WHERE ROWNUM>(@pageIndex-1)*@pageSize AND ROWNUM<=@pageIndex*@pageSize SELECT @RecordCount=COUNT(4) FROM {2}(NOLOCK){3}", fields, sorts, FormatDB(tableName, dbType), filter, primaryKey);

            return sql;
        }

        private static string GetOraclePageViewSql(int? pageIndex, int? pageSize, string fields, string filter, string sorts, string tableName, string primaryKey, DBType dbType)
        {
            string sql = "";

            if (!string.IsNullOrEmpty(sorts))
            {
                sorts = " ORDER BY " + sorts;
            }
            if (!string.IsNullOrEmpty(filter))
            {
                filter = string.Format(CultureInfo.InvariantCulture, " WHERE {0}", filter);
            }
            if (pageIndex == 1)
            {
                sql =
                    string.Format(CultureInfo.InvariantCulture, "Select * From (Select A.*,ROWNUM RN From (Select {0} From {1} {2} {3}) A Where ROWNUM<=:pageSize) Where RN>=:pageIndex", fields, FormatDB(tableName, dbType), filter, sorts);
            }
            else
            {
                sql =
                   string.Format(CultureInfo.InvariantCulture, "Select * From (Select A.*,ROWNUM RN From (Select {0} From {1} {2} {3}) A Where ROWNUM<=:pageSize*:pageIndex) Where RN>=:pageSize*:pageIndex-:pageSize+1", fields, FormatDB(tableName, dbType), filter, sorts);
            }

            return sql;
        }
        public static void FormatArg(ref int? pageIndex, ref int? pageSize, ref int? recordCount, ref string fields, ref string filter, ref string sorts, string primaryKey)
        {
            pageSize = BaseHelper.GetDefaultValue(pageSize, 1000000);

            pageIndex = BaseHelper.GetDefaultValue(pageIndex, 0);

            filter = BaseHelper.GetDefaultValue(filter, "1=1");

            fields = BaseHelper.GetDefaultValue(fields, "*");

            recordCount = BaseHelper.GetDefaultValue(recordCount, -1);

            if (primaryKey != null)
            {
                sorts = BaseHelper.GetDefaultValue(sorts, primaryKey + " ASC");
            }
        }


        public static void FormatArg(ref int? pageIndex, ref int? pageSize, ref string fields, ref string filter, ref string sorts, string primaryKey)
        {
            pageSize = BaseHelper.GetDefaultValue(pageSize, 1000000);

            pageIndex = BaseHelper.GetDefaultValue(pageIndex, 0);

            filter = BaseHelper.GetDefaultValue(filter, "1=1");

            fields = BaseHelper.GetDefaultValue(fields, "*");

            if (primaryKey != null)
            {
                sorts = BaseHelper.GetDefaultValue(sorts, primaryKey + " ASC");
            }
        }

    }
}
